from pymysql import Connection
import matplotlib.pyplot as plt
import numpy as np
plt.rcParams['font.sans-serif']=['SimHei']#字体设置为黑体，若不设置将无法显示中文


# 找出所有已有评分的电影
def analysis_movie_score():
    # 构建Mysql数据库的连接
    conn = Connection(
        host='localhost',  # 主机名
        port=3306,  # 端口号
        user="root",  # 用户名
        password="admin"  # 密码
    )
    # 获取游标对象
    cursor = conn.cursor()
    # 选择数据库
    conn.select_db("spider")
    # 执行查询性sql
    cursor.execute("select m_name,m_score,m_type from t_movie where m_score!='None'")
    # 返回结果是一个嵌套元组
    results_tuple = cursor.fetchall()
    return results_tuple

# 计算最大分数的电影、最小分数的电影、平均分数
def max_min_avg_score(results):
    max = 0.0
    min = 10.0
    avg = 0.0
    for result in results:
        score = float(result[1])
        if max < score:
            max = score
        if min > score:
            min = score
        avg += score
    max_movies = list()
    min_movies = list()
    for result in results:
        score = float(result[1])
        if max == score:
            max_movies.append(result[0])
        if min == score:
            min_movies.append(result[0])
    avg = avg / len(results)
    return {'max_score': (max, max_movies), 'min_score': (min, min_movies), 'avg_score': avg}

# 图形化展示评分详情
def draw_score_analysis(results,result_dict):
    # 准备坐标数据
    names=list()
    scores=list()
    for result in results:
        score = float(result[1])
        names.append(result[0])
        scores.append(score)
    # 绘制柱状图
    draw_plot(names,scores,'电影评分分析','影片','分数',result_dict,True)

# 计算每种类型电影的平均分
def type_score_count(results):
    # 存放每个类型对应的评分 type:score
    type_value={}
    for result in results:
        score=float(result[1])
        # 将类型组成一个列表
        types=result[2].split(',')
        for type in types:
            if type in type_value:
                # 修改类型评分
                type_value[type]=(score+type_value[type])/2
            else:
                # 增加类型评分
                type_value[type]=score
    return type_value

# 计算最大分数的电影类型、最小分数的电影类型、平均分数
def max_min_avg_type_score(type_value):
    # 找出最高分、最低分、平均分
    max = 0.0
    min = 10.0
    avg = 0.0
    # 找出最高分、最低分、平均分
    for type in type_value.keys():
        if max < type_value[type]:
            max = type_value[type]
        if min > type_value[type]:
            min = type_value[type]
        avg = (avg + type_value[type]) / 2
    # 找出对应分数的类型
    max_types = list()
    min_types = list()
    for key in type_value.keys():
        score = type_value[key]
        if max == score:
            max_types.append(key)
        if min == score:
            min_types.append(key)
    return {'max_score': (max, max_types), 'min_score': (min, min_types), 'avg_score': avg}

# 图形化展示类型评分详情
def draw_type_score_analysis(results,result_dict):
    # 准备坐标数据
    names = list(results.keys())
    types = list(results.values())
    # 绘制柱状图
    draw_plot(names, types, '电影类型评分分析', '类型', '分数', result_dict, False)

# 绘制柱状图
def draw_plot(x,y,title,x_label,y_label,result_dict,flag):
    # 绘制柱状图
    plt.figure(figsize=(12, 8))
    p = plt.bar(x, y, width=0.35, color='g')
    # 加入标题
    plt.title(title, fontsize=20)  # fontsize：图表标题字体大小设置
    plt.xlabel(x_label, fontsize=18)  # 坐标轴标题字体大小设置
    plt.ylabel(y_label, fontsize=18)
    # 显示数据在图上
    plt.bar_label(p, label_type='edge')
    if flag:
        plt.xticks(rotation=90)  # 将x轴字体旋转90°
    max_tuple = result_dict['max_score']
    min_tuple = result_dict['min_score']
    # 最高分的电影列表
    max_movies = max_tuple[1]
    # 最低分的电影列表
    min_movies = min_tuple[1]
    # 显示最高分提示
    i = 0
    while i < len(max_movies):
        plt.annotate('最高分', xy=(max_movies[i], max_tuple[0] - 0.2), xytext=(max_movies[i], max_tuple[0] - 0.2))
        i += 1
    # 显示最低分提示
    i = 0
    while i < len(min_movies):
        plt.annotate('最低分', xy=(min_movies[i], min_tuple[0] - 0.2), xytext=(min_movies[i], min_tuple[0] - 0.2))
        i += 1
    # 添加均分的虚线
    plt.axhline(result_dict['avg_score'], linestyle="--", c="r", label='平均分')
    plt.legend(loc=0)
    # 自动调整子图参数,使之填充整个图像区域
    plt.tight_layout()
    plt.show()

if __name__ == '__main__':
    # 找出所有已有评分的电影
    results_tuple = analysis_movie_score()
    # 计算最大分数的电影、最小分数的电影、平均分数
    result_dict = max_min_avg_score(results_tuple)
    # 图形化展示
    draw_score_analysis(results_tuple, result_dict)

    # 计算每种类型电影的平均分
    type_value=type_score_count(results_tuple)
    # 计算最大分数的电影类型、最小分数的电影类型、平均分数
    reselt_dict=max_min_avg_type_score(type_value)
    # 图形化展示
    draw_type_score_analysis(type_value,reselt_dict)